import collections
import openpyxl
from datetime import date, timedelta
import matplotlib.pyplot as plt
import openpyxl
from openpyxl.styles import PatternFill
from openpyxl.styles import Font
from openpyxl.styles import Alignment
import datetime
import pandas as pd
import matplotlib.ticker as mtick
import statistics
import os
import warnings
from dateutil.relativedelta import relativedelta
import copy
import math

predict_add_day = 7
predict_add_month = 6
centerAli = Alignment(horizontal='center', vertical='center')
baseColumn = {1: 'A', 2: 'B', 3: 'C', 4: 'D', 5: 'E', 6: 'F', 7: 'G', 8: 'H', 9: 'I', 10: 'J', 11: 'K', 12: 'L',
              13: 'M', 14: 'N', 15: 'O', 16: 'P', 17: 'Q', 18: 'R', 19: 'S', 20: 'T', 21: 'U', 22: 'V', 23: 'W',
              24: 'X', 25: 'Y', 26: 'Z', 27: 'AA', 28: 'AB', 29: 'AC', 30: 'AD', 31: 'AE', 32: 'AF', 33: 'AG',
              34: 'AH', 35: 'AI', 36: 'AJ', 37: 'AK', 38: 'AL', 39: 'AM', 40: 'AN', 41: 'AO', 42: 'AP', 43: 'AQ',
              44: 'AR', 45: 'AS', 46: 'AT', 47: 'AU', 48: 'AV', 49: 'AW', 50: 'AX', 51: 'AY', 52: 'AZ', 53: 'BA',
              54: 'BB', 55: 'BC', 56: 'BD', 57: 'BE', 58: 'BF', 59: 'BG', 60: 'BH', 61: 'BI', 62: 'BJ', 63: 'BK',
              64: 'BL', 65: 'BM', 66: 'BN', 67: 'BO', 68: 'BP', 69: 'BQ', 70: 'BR', 71: 'BS', 72: 'BT', 73: 'BU',
              74: 'BV', 75: 'BW', 76: 'BX', 77: 'BY', 78: 'BZ', 79: 'CA', 80: 'CB', 81: 'CC', 82: 'CD', 83: 'CE',
              84: 'CF', 85: 'CG', 86: 'CH', 87: 'CI', 88: 'CJ', 89: 'CK', 90: 'CL', 91: 'CM', 92: 'CN', 93: 'CO',
              94: 'CP', 95: 'CQ', 96: 'CR', 97: 'CS', 98: 'CT', 99: 'CU', 100: 'CV', 101: 'CW', 102: 'CX', 103: 'CY',
              104: 'CZ', 105: 'DA', 106: 'DB', 107: 'DC', 108: 'DD', 109: 'DE', 110: 'DF', 111: 'DG', 112: 'DH',
              113: 'DI', 114: 'DJ', 115: 'DK', 116: 'DL', 117: 'DM', 118: 'DN', 119: 'DO', 120: 'DP', 121: 'DQ',
              122: 'DR', 123: 'DS', 124: 'DT', 125: 'DU', 126: 'DV', 127: 'DW', 128: 'DX', 129: 'DY', 130: 'DZ',
              131: 'EA', 132: 'EB', 133: 'EC', 134: 'ED', 135: 'EE', 136: 'EF', 137: 'EG', 138: 'EH', 139: 'EI',
              140: 'EJ', 141: 'EK', 142: 'EL', 143: 'EM', 144: 'EN', 145: 'EO', 146: 'EP', 147: 'EQ', 148: 'ER',
              149: 'ES', 150: 'ET', 151: 'EU', 152: 'EV', 153: 'EW', 154: 'EX', 155: 'EY', 156: 'EZ', 157: 'FA',
              158: 'FB', 159: 'FC', 160: 'FD', 161: 'FE', 162: 'FF', 163: 'FG', 164: 'FH', 165: 'FI', 166: 'FJ',
              167: 'FK', 168: 'FL', 169: 'FM', 170: 'FN', 171: 'FO', 172: 'FP', 173: 'FQ', 174: 'FR', 175: 'FS',
              176: 'FT', 177: 'FU', 178: 'FV', 179: 'FW', 180: 'FX', 181: 'FY', 182: 'FZ', 183: 'GA', 184: 'GB',
              185: 'GC', 186: 'GD', 187: 'GE', 188: 'GF', 189: 'GG', 190: 'GH', 191: 'GI', 192: 'GJ', 193: 'GK',
              194: 'GL', 195: 'GM', 196: 'GN', 197: 'GO', 198: 'GP', 199: 'GQ', 200: 'GR', 201: 'GS', 202: 'GT',
              203: 'GU', 204: 'GV', 205: 'GW', 206: 'GX', 207: 'GY', 208: 'GZ'}

font1 = Font(
    name="宋体",  # 字体
    size=18,  # 字体大小
    # color="0000FF",  # 字体颜色，用16进制rgb表示
    bold=True,  # 是否加粗，True/False
    italic=False,  # 是否斜体，True/False
    strike=None,  # 是否使用删除线，True/False
    underline=None,  # 下划线, 可选'singleAccounting', 'double', 'single', 'doubleAccounting'
)
font2 = Font(
    name="宋体",  # 字体
    size=12,  # 字体大小
    # color="0000FF",  # 字体颜色，用16进制rgb表示
    bold=True,  # 是否加粗，True/False
    italic=False,  # 是否斜体，True/False
    strike=None,  # 是否使用删除线，True/False
    underline=None,  # 下划线, 可选'singleAccounting', 'double', 'single', 'doubleAccounting'
)
font3 = Font(
    name="宋体",  # 字体
    size=12,  # 字体大小
    # color="0000FF",  # 字体颜色，用16进制rgb表示
    bold=False,  # 是否加粗，True/False
    italic=False,  # 是否斜体，True/False
    strike=None,  # 是否使用删除线，True/False
    underline=None,  # 下划线, 可选'singleAccounting', 'double', 'single', 'doubleAccounting'
)


# 时间比较函数
def compareTime(time1, time2):
    fmt = '%Y.%m.%d'
    time1 = datetime.datetime.strptime(time1, fmt)
    time2 = datetime.datetime.strptime(time2, fmt)
    return time1 > time2


'''
datetime 转 时间
'''


def datetimeToTime(time):
    if time.month < 10:
        month = '0' + str(time.month)
    else:
        month = str(time.month)
    if time.day < 10:
        day = '0' + str(time.day)
    else:
        day = str(time.day)
    return str(time.year) + '.' + month + '.' + str(day)


# 按序补足columns
def fillColumns(dict1, cabinetOrigin1, cabinetOrigin2):
    dic = copy.deepcopy(dict1)
    key1 = []
    key2 = []
    Key1AndEta = {}
    Key2AndEta = {}
    if cabinetOrigin1 is not None and len(cabinetOrigin1) > 0:
        key1 = list(cabinetOrigin1.keys())
        key1 = sortedKeys(key1)
        dic = initColumn(dic, key1, '1')
    if cabinetOrigin2 is not None and len(cabinetOrigin2) > 0:
        key2 = list(cabinetOrigin2.keys())
        key2.reverse()
        dic = initColumn(dic, key2, '2')
    for key in key1:
        Key1AndEta[key] = {'eta': cabinetOrigin1[key]['eta'], 'received': cabinetOrigin1[key]['received']}
    for key in key2:
        Key2AndEta[key] = {'eta': cabinetOrigin2[key]['eta'], 'received': cabinetOrigin2[key]['received']}
    return dic, key1, key2, Key1AndEta, Key2AndEta


# 给list按照柜号大小排序
def sortedKeys(keys):
    if keys is not None and len(keys) > 0:
        sorted(keys, key=lambda x: int(x[:x.find('柜')]))
    return keys


'''
补充供应商相对的柜号，同时供应商二得反着来
'''


def initColumn(dic, columnList, originName):
    for column in columnList:
        dic[originName + '-' + column] = ''
    return dic


'''
拿到库存时间和预测时间 -> 现在是时间序列
'''


def getStoreAndPredictTime():
    curTime = date.today()
    cutTimeStart = date.today()
    curTimeFirstWeek = date(curTime.year, curTime.month, 7)
    # 库存columns 从今天开始算三个月的库存
    storeTimeSixMonth = curTime + relativedelta(months=predict_add_month)
    predictTimeEnd = curTime + relativedelta(months=5)
    storeColumns = [curTime]
    predictStartTime = curTime + relativedelta(months=2)  # 预测前推一个月，后推两个月
    predictFirstWeek = date(predictStartTime.year, predictStartTime.month, 7)
    predictColumns = [predictStartTime]  # 预测时间的columns
    # predictColumns = [predictStartTime]  # 预测时间的columns
    # storeColumns.append(curTime)
    # 拿库存时间的columns
    while curTimeFirstWeek < storeTimeSixMonth:
        if cutTimeStart < curTimeFirstWeek <= storeTimeSixMonth:  # 7号
            storeColumns.append(curTimeFirstWeek)
        curTimeFirstWeek = curTimeFirstWeek + timedelta(days=predict_add_day)  # 14号
        if cutTimeStart < curTimeFirstWeek <= storeTimeSixMonth:
            storeColumns.append(curTimeFirstWeek)
        curTimeFirstWeek = curTimeFirstWeek + timedelta(days=predict_add_day)  # 21号
        if cutTimeStart < curTimeFirstWeek <= storeTimeSixMonth:
            storeColumns.append(curTimeFirstWeek)
        curTimeFirstWeek = curTimeFirstWeek + timedelta(days=predict_add_day)  # 28号
        if cutTimeStart < curTimeFirstWeek <= storeTimeSixMonth:
            storeColumns.append(curTimeFirstWeek)
        # 重新到7号 然后加一个月
        curTimeFirstWeek = date(curTimeFirstWeek.year, curTimeFirstWeek.month, 7)
        curTimeFirstWeek = curTimeFirstWeek + relativedelta(months=1)

    # 预测时间的columns
    while predictFirstWeek < predictTimeEnd:
        if predictStartTime < predictFirstWeek <= predictTimeEnd:  # 7号
            predictColumns.append(predictFirstWeek)
        predictFirstWeek = predictFirstWeek + timedelta(days=predict_add_day)  # 14号
        if predictStartTime < predictFirstWeek <= predictTimeEnd:  # 7号
            predictColumns.append(predictFirstWeek)
        predictFirstWeek = predictFirstWeek + timedelta(days=predict_add_day)  # 21号
        if predictStartTime < predictFirstWeek <= predictTimeEnd:  # 7号
            predictColumns.append(predictFirstWeek)
        predictFirstWeek = predictFirstWeek + timedelta(days=predict_add_day)  # 28号
        if predictStartTime < predictFirstWeek <= predictTimeEnd:  # 7号
            predictColumns.append(predictFirstWeek)
        # 重新到7号 然后加一个月
        predictFirstWeek = date(predictFirstWeek.year, predictFirstWeek.month, 7)
        predictFirstWeek = predictFirstWeek + relativedelta(months=1)
    return storeColumns, predictColumns


'''
补上剩余所有的列名
'''


def fillRemainColumns(dic, columnsList1, columnsList2):
    dic['in transit'] = ''
    dic['实际in transit'] = ''
    dic['未装柜'] = ''
    # 预计库存-》 六个月
    for column in columnsList1:
        dic[column] = ''
    # 安全库存和月销
    dic['safetyStock'] = ''
    dic['monthlySales'] = ''
    # 预定-》 前推一个月，后推两个月
    for column in columnsList2:
        dic[column] = ''


'''
进货单sheetname表格长度不一样，新的column和原来的长度要一致
'''


def fillSheetNameNewColumnsPadding(columnsNew, columnsPre):
    idx = 1
    while len(columnsNew) < len(columnsPre):
        columnsNew.append('del' + str(idx))
        idx += 1


'''
String date 转成date
'''


def dateStringToDateTime(time):
    fmt = '%Y.%m.%d'
    return datetime.datetime.strptime(time, fmt).date()


'''
datetime 转成string
'''


def dateTimetoStringDate(time):
    str(time.year) + '.' + str(time.month) + '.' + str(time.day)


'''
拿到进货单里的前缀和  
'''


def getPreSumPurchase(purchaseDic, calculateDataDic, provideName, ifcalculate=False, storeColumnList=[]):
    # dic里面是 一个sku 对应一个进货单里的数据,所以要先拿到所有的sku，把所有的sku都初始化
    for key in purchaseDic.keys():
        # 单独的一个进货单数据
        row = purchaseDic[key]
        eta = row['eta']
        eta = dateStringToDateTime(eta)
        received = row['received']
        cabinetLoading = row['cabinetLoading']
        for skuKey in row.keys():
            # 其他的key 都是sku
            if skuKey == 'eta' or skuKey == 'received' or skuKey == 'cabinetLoading':
                continue
            dicTemp = {}  # 当前临时的dic
            # 如果这个sku对应的calculateDic中有数据了，那就拿出来
            if skuKey in calculateDataDic:
                dicTemp = calculateDataDic[skuKey]

            # 看当前这个日期有数据没，没有的话就新建
            if eta in dicTemp:
                dicTemp[eta] += row[skuKey]
            else:
                dicTemp[eta] = row[skuKey]

            # 计算sku的in transit 和 实际transit
            if not received:  # 如果到货了，那就不需要计算in transit / 实际in transit 了
                if cabinetLoading:
                    fillDicValue(dicTemp, '实际in transit', row[skuKey])
                else:
                    if provideName == '1':
                        fillDicValue(dicTemp, 'oushen未装柜', row[skuKey])
                    else:
                        fillDicValue(dicTemp, 'family未装柜', row[skuKey])
                fillDicValue(dicTemp, 'in transit', row[skuKey])
            calculateDataDic[skuKey] = dicTemp
        # print(calculateDataDic)
    # 接下来做前缀和，需要一个columnlist
    if ifcalculate:
        for skuKeyCal in calculateDataDic.keys():
            dicTempCal = calculateDataDic[skuKeyCal]
            oushen = getFromDic(dicTempCal, 'oushen未装柜')
            family = getFromDic(dicTempCal, 'family未装柜')
            inTransit = getFromDic(dicTempCal, 'in transit')
            actualIntransit = getFromDic(dicTempCal, '实际in transit')
            new_dic = {k: v for k, v in dicTempCal.items() if
                       k not in ['oushen未装柜', 'family未装柜', 'in transit', '实际in transit']}
            sortedKey = sorted(new_dic.keys())
            new_dic_sorted = {k: new_dic[k] for k in sortedKey}
            # print(new_dic_sorted)
            # 开始根据storeColumn计算前缀和
            pre = 0
            for idx, storeKey in enumerate(storeColumnList):
                if storeKey in new_dic_sorted:
                    new_dic_sorted[storeKey] = new_dic_sorted[storeKey] + pre
                    pre = new_dic_sorted[storeKey]
                else:
                    new_dic_sorted[storeKey] = pre
            # print(new_dic_sorted)
            new_dic_sorted['oushen未装柜'] = oushen
            new_dic_sorted['family未装柜'] = family
            new_dic_sorted['in transit'] = inTransit
            new_dic_sorted['实际in transit'] = actualIntransit
            calculateDataDic[skuKeyCal] = new_dic_sorted
    return calculateDataDic


'''
初始化计算数据前缀和的初始化字典
'''

# def getInitCalculateDic(skuList,)


'''
填dic
'''


def fillDicValue(dic, columns, value):
    if columns in dic:
        dic[columns] += value
    else:
        dic[columns] = value


'''
从dic中拿值，没有就填0
'''


def getFromDic(dic, columns):
    if columns in dic:
        return dic[columns]
    return 0


'''
一天一天拿到数据
'''


def getDetailColumns():
    today = datetime.datetime.now()
    endDay = today + relativedelta(months=6)
    column = []
    while today < endDay:
        column.append(today.date())
        today += relativedelta(days=1)
    return column


def optionYxl(OptionSheet, cellName, value, font, mergeFlag=False, mergeStart=0, mergeEnd=0,
              isDescription=False, color='fce4d3', width=30, changeWidth=False, widthColumn='', colorFlag=False):
    if mergeFlag:
        OptionSheet.merge_cells(mergeStart + ':' + mergeEnd)
    OptionSheet[cellName] = value
    if isDescription:
        OptionSheet[cellName].fill = PatternFill(start_color=color, end_color=color, fill_type='solid')
    OptionSheet[cellName].font = font
    OptionSheet[cellName].alignment = centerAli
    if changeWidth:
        OptionSheet.column_dimensions[widthColumn].width = width
    if colorFlag:
        OptionSheet[cellName].fill = PatternFill(start_color=color, end_color=color, fill_type='solid')


'''
拿到天数差值，并返回
'''


def getGapfromDay(time, sales):
    dataTime = dateStringToDateTime(time)
    curTime = datetime.datetime.now()
    return (dataTime - curTime).days / 30 * sales


def openyxl_createFile(fileName):
    workbook = openpyxl.Workbook()
    sheetCreate = workbook.active
    workbook.save(fileName)
    # return sheet


'''
填年份
'''


def optionYear(sheet, inColumn, inRow, storeColumns, predictColumns):
    preYear = 0
    for idx, value in enumerate(storeColumns):
        curYear = value.year
        if preYear != curYear:
            optionYxl(sheet, baseColumn[inColumn] + str(inRow), curYear, font3, colorFlag=True, color='ff0000')
            preYear = curYear
        inColumn += 1
    inColumn += 3
    preYear = 0
    for idx, value in enumerate(predictColumns):
        curYear = value.year
        if preYear != curYear:
            optionYxl(sheet, baseColumn[inColumn] + str(inRow), curYear, font3, colorFlag=True, color='fe5449')
            preYear = curYear
        inColumn += 1


'''
填时间
'''


def optionStoreTimeAndPredictTime(sheet, inColumn, inRow, storeColumns, predictColumns, preColumn, Key1AndEta,
                                  Key2AndEta, transitColumn):
    # 基础信息
    for firstColumn in preColumn:
        optionYxl(sheet, baseColumn[inColumn] + str(inRow), firstColumn, font3, width=20, changeWidth=True,
                  widthColumn=baseColumn[inColumn], colorFlag=True, color='ffd996')
        inColumn += 1
    #  一号货柜
    for originKey1 in Key1AndEta.keys():
        color1 = 'fff2cc'
        if Key1AndEta[originKey1]['received']:
            color1 = '808080'  # 已经收到货了
        optionYxl(sheet, baseColumn[inColumn] + str(inRow - 1), Key1AndEta[originKey1]['eta'], font3, colorFlag=True,
                  color='fff2cc', width=20, changeWidth=True, widthColumn=baseColumn[inColumn])
        optionYxl(sheet, baseColumn[inColumn] + str(inRow), originKey1, font3, colorFlag=True, color=color1)
        inColumn += 1
    optionYxl(sheet, baseColumn[inColumn] + str(inRow), 'oushen未装柜', font3, colorFlag=True,
              color='fff2cc', width=20, changeWidth=True, widthColumn=baseColumn[inColumn])
    inColumn += 1
    # 二号货柜
    for originKey2 in Key2AndEta.keys():
        color1 = 'e2efda'
        if Key2AndEta[originKey2]['received']:
            color1 = '808080'  # 已经收到货了
        optionYxl(sheet, baseColumn[inColumn] + str(inRow - 1), Key2AndEta[originKey2]['eta'], font3, colorFlag=True,
                  color='e2efda')
        optionYxl(sheet, baseColumn[inColumn] + str(inRow), originKey2, font3, width=35, changeWidth=True,
                  widthColumn=baseColumn[inColumn], colorFlag=True, color='e2efda')
        inColumn += 1
    optionYxl(sheet, baseColumn[inColumn] + str(inRow), 'family未装柜', font3, colorFlag=True,
              color='e2efda', width=20, changeWidth=True, widthColumn=baseColumn[inColumn])
    inColumn += 1
    # 预测库存
    # 先填年份
    optionYear(sheet, inColumn, inRow - 1, storeColumns, predictColumns)
    for idx, value in enumerate(storeColumns):
        month, day = getMonthandDay(value)
        optionYxl(sheet, baseColumn[inColumn] + str(inRow), month + '.' + day, font3, colorFlag=True, color='ff0000')
        inColumn += 1
    #     transit
    for transitKey in transitColumn:
        optionYxl(sheet, baseColumn[inColumn] + str(inRow), transitKey, font3, color='ffd996', colorFlag=True)
        inColumn += 1
    #     预测下单
    for idx, value in enumerate(predictColumns):
        month, day = getMonthandDay(value)
        optionYxl(sheet, baseColumn[inColumn] + str(inRow), month + '.' + day, font3, color='fe5449', colorFlag=True)
        inColumn += 1


def getMonthandDay(time):
    month = time.month
    day = time.day
    if month < 10:
        month = '0' + str(month)
    else:
        month = str(month)
    if day < 10:
        day = '0' + str(day)
    else:
        day = str(day)
    return month, day


'''
填
'''


def optionFirstRow(sheet, outColumn, outRow, storeColumns, predictColumns, preColumn, originKeys1,
                   originKeys2, transitColumn):
    optionYxl(sheet, baseColumn[outColumn] + str(outRow), '基础信息', font1, True,
              baseColumn[outColumn] + str(outRow), baseColumn[outColumn + len(preColumn) - 1] + str(outRow),
              colorFlag=True, color='ffd996')
    outColumn += len(preColumn)
    optionYxl(sheet, baseColumn[outColumn] + str(outRow), '供应商1号货柜', font1, True,
              baseColumn[outColumn] + str(outRow), baseColumn[outColumn + len(originKeys1)] + str(outRow),
              colorFlag=True, color='fff2cc')
    outColumn += len(originKeys1) + 1
    optionYxl(sheet, baseColumn[outColumn] + str(outRow), '供应商2号货柜', font1, True,
              baseColumn[outColumn] + str(outRow), baseColumn[outColumn + len(originKeys2)] + str(outRow),
              colorFlag=True, color='e2efda')
    # 越过in transit
    outColumn += len(originKeys2) + 1
    optionYxl(sheet, baseColumn[outColumn] + str(outRow), '预测库存', font1, True,
              baseColumn[outColumn] + str(outRow), baseColumn[outColumn + len(storeColumns) - 1] + str(outRow),
              colorFlag=True, color='ff0000')
    outColumn += len(storeColumns)
    optionYxl(sheet, baseColumn[outColumn] + str(outRow), '在途统计', font1, True,
              baseColumn[outColumn] + str(outRow), baseColumn[outColumn + len(transitColumn) - 1] + str(outRow),
              colorFlag=True, color='ffd996')
    outColumn += len(transitColumn)
    optionYxl(sheet, baseColumn[outColumn] + str(outRow), '预测下单', font1, True,
              baseColumn[outColumn] + str(outRow), baseColumn[outColumn + len(predictColumns) - 1] + str(outRow),
              colorFlag=True, color='fe5449')


'''
数据填充
'''


def optionData(sheet, inColumn, inRow, skuDic, storeColumns, predictColumns, preColumn, cabinetDicOringin1,
               cabinetDicOringin2, transitColumn, originKeys1, originKeys2, preSumOrigin, saleDic={}):
    # rowKey 就是sku
    skuOrder = calculatePosition(skuDic.keys(), saleDic.keys())
    for rowKey in skuOrder:
        # existButNoValue = False
        saleAvg = 0
        # print(rowKey)
        if rowKey in saleDic:
            saleAvg = saleDic[rowKey]['sales']
            if math.isnan(saleAvg):
                saleAvg = 0
                # existButNoValue = True
        rowValue = skuDic[rowKey]
        # 实施更新当前sku库存
        newStore = 0
        if rowKey in saleDic:
            newStore = saleDic[rowKey]['store']
        rowValue['Quantity On Hand'] = newStore
        inColumn = 2
        # 先填基本信息
        for preKey in preColumn:
            value = rowValue[preKey]
            if value is None:
                value = ''
            if preKey == 'Product/Service Name':
                optionYxl(sheet, baseColumn[inColumn] + str(inRow), value, font3, changeWidth=True,
                          widthColumn=baseColumn[inColumn], width=120)
            elif preKey == 'Sales Description':
                optionYxl(sheet, baseColumn[inColumn] + str(inRow), value, font3, changeWidth=True,
                          widthColumn=baseColumn[inColumn], width=80)
            else:
                optionYxl(sheet, baseColumn[inColumn] + str(inRow), value, font3)
            inColumn += 1
        # 开始填第一个货柜
        for key1 in originKeys1:
            key1Data = cabinetDicOringin1[key1]
            if rowKey in key1Data:
                value = key1Data[rowKey]
                oushenValue = preSumOrigin[rowKey]['oushen未装柜']
            else:
                value = ''
                oushenValue = ''
            optionYxl(sheet, baseColumn[inColumn] + str(inRow), value, font3)
            inColumn += 1
        optionYxl(sheet, baseColumn[inColumn] + str(inRow), oushenValue, font3)
        inColumn += 1
        # 开始第二个供应商货柜
        for key2 in originKeys2:
            key2Data = cabinetDicOringin2[key2]
            if rowKey in key2Data:
                value = key2Data[rowKey]
                oushenValue = preSumOrigin[rowKey]['family未装柜']
            else:
                value = ''
                oushenValue = ''
            optionYxl(sheet, baseColumn[inColumn] + str(inRow), value, font3)
            inColumn += 1
        optionYxl(sheet, baseColumn[inColumn] + str(inRow), oushenValue, font3)
        inColumn += 1

        # 开始填store

        for timeColumn in storeColumns:
            if rowKey in preSumOrigin:
                # print(preSumOrigin)
                value = int(preSumOrigin[rowKey][timeColumn])
            else:
                value = 0
            btwDays = (timeColumn - datetime.datetime.now().date()).days

            # sales = btwDays * skuSaleDic[rowKey]['sale]
            sales = round(btwDays * saleAvg / 30, 2)
            predictStore = rowValue['Quantity On Hand'] - sales + value
            optionYxl(sheet, baseColumn[inColumn] + str(inRow), predictStore, font3)
            inColumn += 1

        # 在途统计
        if rowKey in preSumOrigin:
            optionYxl(sheet, baseColumn[inColumn] + str(inRow), preSumOrigin[rowKey]['in transit'], font3)
            inColumn += 1
            optionYxl(sheet, baseColumn[inColumn] + str(inRow), preSumOrigin[rowKey]['实际in transit'], font3)
            inColumn += 1
            optionYxl(sheet, baseColumn[inColumn] + str(inRow),
                      preSumOrigin[rowKey]['oushen未装柜'] + preSumOrigin[rowKey]['family未装柜'], font3)
            inColumn += 1
        else:
            inColumn += 3
        # 预测订单
        colorCount = 0
        colorStore = '000000'
        for preditTime in predictColumns:
            if rowKey in preSumOrigin:
                preditTimeValue = int(preSumOrigin[rowKey][preditTime])
            else:
                preditTimeValue = 0
            btwDays = (preditTime - datetime.datetime.now().date()).days
            if btwDays >= 90:
                if colorCount == 0:
                    colorStore = 'fe5449'
                    colorCount += 1
                else:
                    colorStore = '000000'
            sales = round(btwDays * saleAvg / 30, 2)
            # sales = btwDays
            predictStore = rowValue['Quantity On Hand'] - sales + preditTimeValue
            valueInput = saleAvg * 3 - predictStore
            if valueInput < 0:
                valueInput = 0
            # colorFinal = '000000'
            # if existButNoValue:
            #     colorFinal = 'ffff00'
            optionYxl(sheet, baseColumn[inColumn] + str(inRow), valueInput, font3, color=colorStore, colorFlag=True)
            inColumn += 1
        inRow += 1


def getSaleDic(salesDf):
    saleDic = {}
    for idx, rowKey in salesDf.iterrows():
        saleDic[rowKey['SKU']] = rowKey
    return saleDic


'''
对齐sku
list1 是乱的SkuList中的sku
list2 是storeAndExpireTime中的sku
'''


def calculatePosition(list1, list2):
    commonList = [value for value in list1 if value in list2 and value is not None]
    uniqueList = [value for value in list1 if value not in list2 and value is not None]
    listRet = []
    for key in list2:
        if key in commonList:
            listRet.append(key)
    for key in uniqueList:
        listRet.append(key)
    return listRet
